package com.kaishengit.util;

import java.io.IOException;
import java.io.InputStream;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Properties;

import org.apache.commons.dbcp.BasicDataSource;


public class DBHelp<T> {

	private static BasicDataSource ds;

	static{

		InputStream stream = DBHelp.class.getClassLoader().getResourceAsStream("db.properties");

		Properties p = new Properties();
		try {
			p.load(stream);

			ds = new BasicDataSource();
			ds.setUrl(p.getProperty("url"));
			ds.setPassword(p.getProperty("pwd"));
			ds.setDriverClassName(p.getProperty("driver"));
			ds.setUsername(p.getProperty("name"));
			ds.setInitialSize(5);
			ds.setMaxActive(20);
			ds.setMaxWait(5000);
			ds.setMinIdle(8);

		} catch (IOException e) {
			e.printStackTrace();
		}

	}

	public Connection getConnection() {
		Connection conn =null;
		try {
			conn = ds.getConnection();
		}  catch (SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}
	public Object executeForFunction(String sql,Object...args ){
		PreparedStatement sta = null;
		Connection conn = null;
		ResultSet res = null;
		Object result = null ;
		try {
			conn = getConnection();
			sta = conn.prepareStatement(sql);
			for(int i = 0;i<args.length;i++){
				sta.setObject(i+1, args[i]);
			}
			res = sta.executeQuery();
			if(res.next()){
				result = res.getObject(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally{
			close(res,sta,conn);
		}
		return result;
	}

	public List<T> executeQueryForList(String sql,RowMapper<T> rm,Object...args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		List<T> list = new ArrayList<T>();
		try {
			conn = getConnection();

			stat = conn.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}


			rs = stat.executeQuery();
			while(rs.next()) {
				T obj = rm.mapRow(rs);
				list.add(obj);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return list;
	}


	public T executeQueryForObject(String sql,RowMapper<T> rm,Object...args) {
		Connection conn = null;
		PreparedStatement stat = null;
		ResultSet rs = null;
		T obj = null;

		try {
			conn = getConnection();

			stat = conn.prepareStatement(sql);

			for (int i = 0; i < args.length; i++) {
				stat.setObject(i+1, args[i]);
			}
			rs = stat.executeQuery();
			while(rs.next()) {
				obj = rm.mapRow(rs);
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(rs, stat, conn);
		}
		return obj;
	}


	public boolean executeSQL(String sql,Object... args) {
		Connection conn = null;
		PreparedStatement stat = null;

		try {
			conn = getConnection();


			stat = conn.prepareStatement(sql);
			for (int i = 0; i < args.length; i++) {
				stat.setObject(i + 1, args[i]);
			}

			int rows = stat.executeUpdate();
			if(rows > 0) {
				return true;
			}
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			close(stat,conn);
		}

		return false;
	}

	public void close(ResultSet rs, Statement stat, Connection conn) {
		try {
			if(rs != null) {
				rs.close();
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if(stat != null) {
					stat.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				try {
					if(conn != null) {
						conn.close();
					}
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}


	public void close(Statement stat,Connection conn) {
		close(null,stat,conn);
	}

}